{
 "metadata": {
  "name": "",
  "signature": "sha256:9b3c55b1214330b9560e36ed5ace2e2bd26f9c642589d6c53379f139bc98862d"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Data Wrangling: Clean, Transform, Merge, Reshape"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from __future__ import division\n",
      "from numpy.random import randn\n",
      "import numpy as np\n",
      "import os\n",
      "import matplotlib.pyplot as plt\n",
      "np.random.seed(12345)\n",
      "plt.rc('figure', figsize=(10, 6))\n",
      "from pandas import Series, DataFrame\n",
      "import pandas\n",
      "import pandas as pd\n",
      "np.set_printoptions(precision=4, threshold=500)\n",
      "pd.options.display.max_rows = 100"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%matplotlib inline"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%cd ../book_scripts"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Combining and merging data sets"
     ]
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Database-style DataFrame merges"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df1 = DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],\n",
      "                 'data1': range(7)})\n",
      "df2 = DataFrame({'key': ['a', 'b', 'd'],\n",
      "                 'data2': range(3)})\n",
      "df1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df2"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df1, df2)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df1, df2, on='key')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df3 = DataFrame({'lkey': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],\n",
      "                 'data1': range(7)})\n",
      "df4 = DataFrame({'rkey': ['a', 'b', 'd'],\n",
      "                 'data2': range(3)})\n",
      "pd.merge(df3, df4, left_on='lkey', right_on='rkey')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df1, df2, how='outer')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df1 = DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'b'],\n",
      "                 'data1': range(6)})\n",
      "df2 = DataFrame({'key': ['a', 'b', 'a', 'b', 'd'],\n",
      "                 'data2': range(5)})"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df2"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df1, df2, on='key', how='left')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df1, df2, how='inner')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "left = DataFrame({'key1': ['foo', 'foo', 'bar'],\n",
      "                  'key2': ['one', 'two', 'one'],\n",
      "                  'lval': [1, 2, 3]})\n",
      "right = DataFrame({'key1': ['foo', 'foo', 'bar', 'bar'],\n",
      "                   'key2': ['one', 'one', 'one', 'two'],\n",
      "                   'rval': [4, 5, 6, 7]})\n",
      "pd.merge(left, right, on=['key1', 'key2'], how='outer')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(left, right, on='key1')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": true,
     "input": [
      "pd.merge(left, right, on='key1', suffixes=('_left', '_right'))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Merging on index"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "left1 = DataFrame({'key': ['a', 'b', 'a', 'a', 'b', 'c'],\n",
      "                  'value': range(6)})\n",
      "right1 = DataFrame({'group_val': [3.5, 7]}, index=['a', 'b'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "left1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "right1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(left1, right1, left_on='key', right_index=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(left1, right1, left_on='key', right_index=True, how='outer')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "lefth = DataFrame({'key1': ['Ohio', 'Ohio', 'Ohio', 'Nevada', 'Nevada'],\n",
      "                   'key2': [2000, 2001, 2002, 2001, 2002],\n",
      "                   'data': np.arange(5.)})\n",
      "righth = DataFrame(np.arange(12).reshape((6, 2)),\n",
      "                   index=[['Nevada', 'Nevada', 'Ohio', 'Ohio', 'Ohio', 'Ohio'],\n",
      "                          [2001, 2000, 2000, 2000, 2001, 2002]],\n",
      "                   columns=['event1', 'event2'])\n",
      "lefth"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "righth"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(lefth, righth, left_on=['key1', 'key2'], right_index=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(lefth, righth, left_on=['key1', 'key2'],\n",
      "         right_index=True, how='outer')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "left2 = DataFrame([[1., 2.], [3., 4.], [5., 6.]], index=['a', 'c', 'e'],\n",
      "                 columns=['Ohio', 'Nevada'])\n",
      "right2 = DataFrame([[7., 8.], [9., 10.], [11., 12.], [13, 14]],\n",
      "                   index=['b', 'c', 'd', 'e'], columns=['Missouri', 'Alabama'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "left2"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "right2"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(left2, right2, how='outer', left_index=True, right_index=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "left2.join(right2, how='outer')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "left1.join(right1, on='key')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "another = DataFrame([[7., 8.], [9., 10.], [11., 12.], [16., 17.]],\n",
      "                    index=['a', 'c', 'e', 'f'], columns=['New York', 'Oregon'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "left2.join([right2, another])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "left2.join([right2, another], how='outer')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Concatenating along an axis"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "arr = np.arange(12).reshape((3, 4))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "arr"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "np.concatenate([arr, arr], axis=1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s1 = Series([0, 1], index=['a', 'b'])\n",
      "s2 = Series([2, 3, 4], index=['c', 'd', 'e'])\n",
      "s3 = Series([5, 6], index=['f', 'g'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.concat([s1, s2, s3])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.concat([s1, s2, s3], axis=1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s4 = pd.concat([s1 * 5, s3])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.concat([s1, s4], axis=1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.concat([s1, s4], axis=1, join='inner')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.concat([s1, s4], axis=1, join_axes=[['a', 'c', 'b', 'e']])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result = pd.concat([s1, s1, s3], keys=['one', 'two', 'three'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Much more on the unstack function later\n",
      "result.unstack()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.concat([s1, s2, s3], axis=1, keys=['one', 'two', 'three'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df1 = DataFrame(np.arange(6).reshape(3, 2), index=['a', 'b', 'c'],\n",
      "                columns=['one', 'two'])\n",
      "df2 = DataFrame(5 + np.arange(4).reshape(2, 2), index=['a', 'c'],\n",
      "                columns=['three', 'four'])\n",
      "pd.concat([df1, df2], axis=1, keys=['level1', 'level2'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.concat({'level1': df1, 'level2': df2}, axis=1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.concat([df1, df2], axis=1, keys=['level1', 'level2'],\n",
      "          names=['upper', 'lower'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df1 = DataFrame(np.random.randn(3, 4), columns=['a', 'b', 'c', 'd'])\n",
      "df2 = DataFrame(np.random.randn(2, 3), columns=['b', 'd', 'a'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df2"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.concat([df1, df2], ignore_index=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Combining data with overlap"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a = Series([np.nan, 2.5, np.nan, 3.5, 4.5, np.nan],\n",
      "           index=['f', 'e', 'd', 'c', 'b', 'a'])\n",
      "b = Series(np.arange(len(a), dtype=np.float64),\n",
      "           index=['f', 'e', 'd', 'c', 'b', 'a'])\n",
      "b[-1] = np.nan"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "b"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "np.where(pd.isnull(a), b, a)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "b[:-2].combine_first(a[2:])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df1 = DataFrame({'a': [1., np.nan, 5., np.nan],\n",
      "                 'b': [np.nan, 2., np.nan, 6.],\n",
      "                 'c': range(2, 18, 4)})\n",
      "df2 = DataFrame({'a': [5., 4., np.nan, 3., 7.],\n",
      "                 'b': [np.nan, 3., 4., 6., 8.]})\n",
      "df1.combine_first(df2)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Reshaping and pivoting"
     ]
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Reshaping with hierarchical indexing"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data = DataFrame(np.arange(6).reshape((2, 3)),\n",
      "                 index=pd.Index(['Ohio', 'Colorado'], name='state'),\n",
      "                 columns=pd.Index(['one', 'two', 'three'], name='number'))\n",
      "data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result = data.stack()\n",
      "result"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result.unstack()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result.unstack(0)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result.unstack('state')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s1 = Series([0, 1, 2, 3], index=['a', 'b', 'c', 'd'])\n",
      "s2 = Series([4, 5, 6], index=['c', 'd', 'e'])\n",
      "data2 = pd.concat([s1, s2], keys=['one', 'two'])\n",
      "data2.unstack()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data2.unstack().stack()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data2.unstack().stack(dropna=False)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = DataFrame({'left': result, 'right': result + 5},\n",
      "               columns=pd.Index(['left', 'right'], name='side'))\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.unstack('state')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.unstack('state').stack('side')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Pivoting \"long\" to \"wide\" format"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data = pd.read_csv('ch07/macrodata.csv')\n",
      "periods = pd.PeriodIndex(year=data.year, quarter=data.quarter, name='date')\n",
      "data = DataFrame(data.to_records(),\n",
      "                 columns=pd.Index(['realgdp', 'infl', 'unemp'], name='item'),\n",
      "                 index=periods.to_timestamp('D', 'end'))\n",
      "\n",
      "ldata = data.stack().reset_index().rename(columns={0: 'value'})\n",
      "wdata = ldata.pivot('date', 'item', 'value')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ldata[:10]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pivoted = ldata.pivot('date', 'item', 'value')\n",
      "pivoted.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ldata['value2'] = np.random.randn(len(ldata))\n",
      "ldata[:10]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pivoted = ldata.pivot('date', 'item')\n",
      "pivoted[:5]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pivoted['value'][:5]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "unstacked = ldata.set_index(['date', 'item']).unstack('item')\n",
      "unstacked[:7]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Data transformation"
     ]
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Removing duplicates"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data = DataFrame({'k1': ['one'] * 3 + ['two'] * 4,\n",
      "                  'k2': [1, 1, 2, 3, 3, 4, 4]})\n",
      "data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.duplicated()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.drop_duplicates()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data['v1'] = range(7)\n",
      "data.drop_duplicates(['k1'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.drop_duplicates(['k1', 'k2'], take_last=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Transforming data using a function or mapping"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data = DataFrame({'food': ['bacon', 'pulled pork', 'bacon', 'Pastrami',\n",
      "                           'corned beef', 'Bacon', 'pastrami', 'honey ham',\n",
      "                           'nova lox'],\n",
      "                  'ounces': [4, 3, 12, 6, 7.5, 8, 3, 5, 6]})\n",
      "data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "meat_to_animal = {\n",
      "  'bacon': 'pig',\n",
      "  'pulled pork': 'pig',\n",
      "  'pastrami': 'cow',\n",
      "  'corned beef': 'cow',\n",
      "  'honey ham': 'pig',\n",
      "  'nova lox': 'salmon'\n",
      "}"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data['animal'] = data['food'].map(str.lower).map(meat_to_animal)\n",
      "data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data['food'].map(lambda x: meat_to_animal[x.lower()])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Replacing values"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data = Series([1., -999., 2., -999., -1000., 3.])\n",
      "data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.replace(-999, np.nan)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.replace([-999, -1000], np.nan)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.replace([-999, -1000], [np.nan, 0])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.replace({-999: np.nan, -1000: 0})"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Renaming axis indexes"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data = DataFrame(np.arange(12).reshape((3, 4)),\n",
      "                 index=['Ohio', 'Colorado', 'New York'],\n",
      "                 columns=['one', 'two', 'three', 'four'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.index.map(str.upper)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.index = data.index.map(str.upper)\n",
      "data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.rename(index=str.title, columns=str.upper)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.rename(index={'OHIO': 'INDIANA'},\n",
      "            columns={'three': 'peekaboo'})"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Always returns a reference to a DataFrame\n",
      "_ = data.rename(index={'OHIO': 'INDIANA'}, inplace=True)\n",
      "data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Discretization and binning"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ages = [20, 22, 25, 27, 21, 23, 37, 31, 61, 45, 41, 32]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "bins = [18, 25, 35, 60, 100]\n",
      "cats = pd.cut(ages, bins)\n",
      "cats"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cats.labels"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cats.levels"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.value_counts(cats)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.cut(ages, [18, 26, 36, 61, 100], right=False)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "group_names = ['Youth', 'YoungAdult', 'MiddleAged', 'Senior']\n",
      "pd.cut(ages, bins, labels=group_names)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data = np.random.rand(20)\n",
      "pd.cut(data, 4, precision=2)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data = np.random.randn(1000) # Normally distributed\n",
      "cats = pd.qcut(data, 4) # Cut into quartiles\n",
      "cats"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.value_counts(cats)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.qcut(data, [0, 0.1, 0.5, 0.9, 1.])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Detecting and filtering outliers"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "np.random.seed(12345)\n",
      "data = DataFrame(np.random.randn(1000, 4))\n",
      "data.describe()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "col = data[3]\n",
      "col[np.abs(col) > 3]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data[(np.abs(data) > 3).any(1)]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data[np.abs(data) > 3] = np.sign(data) * 3\n",
      "data.describe()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Permutation and random sampling"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = DataFrame(np.arange(5 * 4).reshape((5, 4)))\n",
      "sampler = np.random.permutation(5)\n",
      "sampler"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.take(sampler)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.take(np.random.permutation(len(df))[:3])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "bag = np.array([5, 7, -1, 6, 4])\n",
      "sampler = np.random.randint(0, len(bag), size=10)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sampler"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "draws = bag.take(sampler)\n",
      "draws"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Computing indicator / dummy variables"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'b'],\n",
      "                'data1': range(6)})\n",
      "pd.get_dummies(df['key'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dummies = pd.get_dummies(df['key'], prefix='key')\n",
      "df_with_dummy = df[['data1']].join(dummies)\n",
      "df_with_dummy"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "mnames = ['movie_id', 'title', 'genres']\n",
      "movies = pd.read_table('ch07/movies.dat', sep='::', header=None,\n",
      "                        names=mnames)\n",
      "movies[:10]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "genre_iter = (set(x.split('|')) for x in movies.genres)\n",
      "genres = sorted(set.union(*genre_iter))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dummies = DataFrame(np.zeros((len(movies), len(genres))), columns=genres)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for i, gen in enumerate(movies.genres):\n",
      "    dummies.ix[i, gen.split('|')] = 1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "movies_windic = movies.join(dummies.add_prefix('Genre_'))\n",
      "movies_windic.ix[0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "np.random.seed(12345)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "values = np.random.rand(10)\n",
      "values"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "bins = [0, 0.2, 0.4, 0.6, 0.8, 1]\n",
      "pd.get_dummies(pd.cut(values, bins))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "String manipulation"
     ]
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "String object methods"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "val = 'a,b,  guido'\n",
      "val.split(',')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pieces = [x.strip() for x in val.split(',')]\n",
      "pieces"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "first, second, third = pieces\n",
      "first + '::' + second + '::' + third"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "'::'.join(pieces)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "'guido' in val"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "val.index(',')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "val.find(':')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "val.index(':')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "val.count(',')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "val.replace(',', '::')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "val.replace(',', '')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Regular expressions"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import re\n",
      "text = \"foo    bar\\t baz  \\tqux\"\n",
      "re.split('\\s+', text)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "regex = re.compile('\\s+')\n",
      "regex.split(text)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "regex.findall(text)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "text = \"\"\"Dave dave@google.com\n",
      "Steve steve@gmail.com\n",
      "Rob rob@gmail.com\n",
      "Ryan ryan@yahoo.com\n",
      "\"\"\"\n",
      "pattern = r'[A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]{2,4}'\n",
      "\n",
      "# re.IGNORECASE makes the regex case-insensitive\n",
      "regex = re.compile(pattern, flags=re.IGNORECASE)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "regex.findall(text)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "m = regex.search(text)\n",
      "m"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "text[m.start():m.end()]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print(regex.match(text))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print(regex.sub('REDACTED', text))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pattern = r'([A-Z0-9._%+-]+)@([A-Z0-9.-]+)\\.([A-Z]{2,4})'\n",
      "regex = re.compile(pattern, flags=re.IGNORECASE)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "m = regex.match('wesm@bright.net')\n",
      "m.groups()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "regex.findall(text)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print(regex.sub(r'Username: \\1, Domain: \\2, Suffix: \\3', text))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "regex = re.compile(r\"\"\"\n",
      "    (?P<username>[A-Z0-9._%+-]+)\n",
      "    @\n",
      "    (?P<domain>[A-Z0-9.-]+)\n",
      "    \\.\n",
      "    (?P<suffix>[A-Z]{2,4})\"\"\", flags=re.IGNORECASE|re.VERBOSE)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "m = regex.match('wesm@bright.net')\n",
      "m.groupdict()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Vectorized string functions in pandas"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data = {'Dave': 'dave@google.com', 'Steve': 'steve@gmail.com',\n",
      "        'Rob': 'rob@gmail.com', 'Wes': np.nan}\n",
      "data = Series(data)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.isnull()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.str.contains('gmail')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pattern"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.str.findall(pattern, flags=re.IGNORECASE)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "matches = data.str.match(pattern, flags=re.IGNORECASE)\n",
      "matches"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "matches.str.get(1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "matches.str[0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.str[:5]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Example: USDA Food Database"
     ]
    },
    {
     "cell_type": "raw",
     "metadata": {},
     "source": [
      "{\n",
      "  \"id\": 21441,\n",
      "  \"description\": \"KENTUCKY FRIED CHICKEN, Fried Chicken, EXTRA CRISPY,\n",
      "Wing, meat and skin with breading\",\n",
      "  \"tags\": [\"KFC\"],\n",
      "  \"manufacturer\": \"Kentucky Fried Chicken\",\n",
      "  \"group\": \"Fast Foods\",\n",
      "  \"portions\": [\n",
      "    {\n",
      "      \"amount\": 1,\n",
      "      \"unit\": \"wing, with skin\",\n",
      "      \"grams\": 68.0\n",
      "    },\n",
      "\n",
      "    ...\n",
      "  ],\n",
      "  \"nutrients\": [\n",
      "    {\n",
      "      \"value\": 20.8,\n",
      "      \"units\": \"g\",\n",
      "      \"description\": \"Protein\",\n",
      "      \"group\": \"Composition\"\n",
      "    },\n",
      "\n",
      "    ...\n",
      "  ]\n",
      "}"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import json\n",
      "db = json.load(open('ch07/foods-2011-10-03.json'))\n",
      "len(db)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "db[0].keys()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "db[0]['nutrients'][0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "nutrients = DataFrame(db[0]['nutrients'])\n",
      "nutrients[:7]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "info_keys = ['description', 'group', 'id', 'manufacturer']\n",
      "info = DataFrame(db, columns=info_keys)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "info[:5]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "info"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.value_counts(info.group)[:10]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "nutrients = []\n",
      "\n",
      "for rec in db:\n",
      "    fnuts = DataFrame(rec['nutrients'])\n",
      "    fnuts['id'] = rec['id']\n",
      "    nutrients.append(fnuts)\n",
      "\n",
      "nutrients = pd.concat(nutrients, ignore_index=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "nutrients"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "nutrients.duplicated().sum()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "nutrients = nutrients.drop_duplicates()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "col_mapping = {'description' : 'food',\n",
      "               'group'       : 'fgroup'}\n",
      "info = info.rename(columns=col_mapping, copy=False)\n",
      "info"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "col_mapping = {'description' : 'nutrient',\n",
      "               'group' : 'nutgroup'}\n",
      "nutrients = nutrients.rename(columns=col_mapping, copy=False)\n",
      "nutrients"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ndata = pd.merge(nutrients, info, on='id', how='outer')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ndata"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ndata.ix[30000]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result = ndata.groupby(['nutrient', 'fgroup'])['value'].quantile(0.5)\n",
      "result['Zinc, Zn'].order().plot(kind='barh')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "by_nutrient = ndata.groupby(['nutgroup', 'nutrient'])\n",
      "\n",
      "get_maximum = lambda x: x.xs(x.value.idxmax())\n",
      "get_minimum = lambda x: x.xs(x.value.idxmin())\n",
      "\n",
      "max_foods = by_nutrient.apply(get_maximum)[['value', 'food']]\n",
      "\n",
      "# make the food a little smaller\n",
      "max_foods.food = max_foods.food.str[:50]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "max_foods.ix['Amino Acids']['food']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}